﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Joomiz.ReleaseCalendar.Models
{
    public class CalendarItem
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Year { get; set; }
        public DateTime DateCreated { get; set; }
    }

    public class PeriodItem
    {
        public int Id { get; set; }
        public int Type { get; set; }
        public string Description { get; set; }
        public DateTime Start { get; set; }
        public DateTime End { get; set; }
    }

    public class PeriodRepository
    {
        public List<PeriodItem> GetPeriodByCalendarId(int id)
        {
            var list = new List<PeriodItem>();

            using (SqlConnection connection = this.CreateConnection())
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT [Id], [PeriodTypeId], [StartDate], [EndDate], [Description] FROM Period WHERE CalendarId = @Id Order By StartDate";

                command.Parameters.AddWithValue("@Id", id);


                SqlDataReader reader = command.ExecuteReader();


                while (reader.Read())
                {
                    var p = new PeriodItem();
                    p.Id = reader.GetInt32(0);
                    p.Type = reader.GetInt32(1);
                    p.Start = reader.GetDateTime(2);
                    p.End = reader.GetDateTime(3);
                    p.Description = reader.GetString(4);
                    list.Add(p);
                }


                connection.Close();
            }

            return list;
        }

        public List<CalendarItem> GetCalendars()
        {
            var list = new List<CalendarItem>();

            using (SqlConnection connection = this.CreateConnection())
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT Id, Name, Year, DateCreated FROM Calendar Order By Year, Name";

                

                SqlDataReader reader = command.ExecuteReader();


                while (reader.Read())
                {
                    var c = new CalendarItem();
                    c.Id = reader.GetInt32(0);
                    c.Name = reader.GetString(1);
                    c.Year = reader.GetInt32(2);
                    c.DateCreated = reader.GetDateTime(3);
                    list.Add(c);
                }


                connection.Close();

                
            }

            return list;
        }


        public List<Period> GetReleasePeriods(int year, string description)
        {
            using (SqlConnection connection = this.CreateConnection())
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT A.StartDate, A.EndDate FROM Period A INNER JOIN Calendar B ON A.CalendarId = B.Id WHERE B.Name = @Name AND B.Year = @Year AND A.PeriodTypeId = 1";

                command.Parameters.AddWithValue("@Name", description);
                command.Parameters.AddWithValue("@Year", year);

                SqlDataReader reader = command.ExecuteReader();

                var list = ReadPeriods(reader);

                connection.Close();

                return list;
            }
        }

        public List<Period> GetFreezingPeriods(int year, string description)
        {
            using (SqlConnection connection = this.CreateConnection())
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT A.StartDate, A.EndDate FROM Period A INNER JOIN Calendar B ON A.CalendarId = B.Id WHERE B.Name = @Name AND B.Year = @Year AND A.PeriodTypeId = 3";

                command.Parameters.AddWithValue("@Name", description);
                command.Parameters.AddWithValue("@Year", year);

                SqlDataReader reader = command.ExecuteReader();

                var list = ReadPeriods(reader);

                connection.Close();

                return list;
            }
        }

        public List<Period> GetInfrastructurePeriods(int year, string description)
        {
            using (SqlConnection connection = this.CreateConnection())
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT A.StartDate, A.EndDate FROM Period A INNER JOIN Calendar B ON A.CalendarId = B.Id WHERE B.Name = @Name AND B.Year = @Year AND A.PeriodTypeId = 2";

                command.Parameters.AddWithValue("@Name", description);
                command.Parameters.AddWithValue("@Year", year);

                SqlDataReader reader = command.ExecuteReader();

                var list = ReadPeriods(reader);

                connection.Close();

                return list;
            }
        }


        private List<Period> ReadPeriods(SqlDataReader reader)
        {
            var list = new List<Period>();

            while (reader.Read())
            {
                list.Add(new Period(reader.GetDateTime(0), reader.GetDateTime(1)));
            }

            return list;
        }

        private SqlConnection CreateConnection()
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["CalendarContext"].ConnectionString;
            return connection;
        }

        
    }
}